SQL statements:

DDL(Data                 DML(Data         TCL(Transaction
Definition Language)    Manipulation        Control Language)
                           Language)
---------------------------------------------------------------
CREATE                  INSERT             COMMIT
RENAME                  UPDATE             ROLLBACK
ALTER                   DELETE             SAVEPOINT
TRUNCATE				
DROP

-----------------------------------------------------------------


Characterstics of DDL Statements:
1. DDL statements are auto saved permanently
2. we cannot rollback (undo) the DDL transaction
3. We cannot apply filter OR WHERE clause for DDL statements


CREATE: It is used to create a table. It accepts 3 arguments
 (a) ColName
 (b) Datatype
 (c) Size

syntax: 
create table <tableName> (<colName> <datatype> <size>,....);
-----------------------------------

RENAME: It is used to rename the table name

sysntax:
 Rename <tableName> to <NewTableName>;
------------------------------------

ALTER: It is used to change the structure of the table which is as follows:

 (a) Add new column to the table
 (b) modify the column datatype
 (c) delete the column from the table
 (d) Rename the column name
 (e) Add, disable, enable & drop the constraint

(a) Add new column to the table:
syntax:
  Alter table <tableName> add (<newColName> <datatype> <size>, ....);


(b) modify the column datatype:
syntax:
   Alter table <tableName> modify (<colName> <newDatatype> <newSize>);



(c) delete the column from the table:
syntax:
 (i)alter table <tableName> drop column <colName>;
 (ii)alter table <tableName> drop (<colName1>,<col2Name2>);



(d) Rename the column name:
syntax:
alter table <tableName> rename column <oldColName> to <newColName>;
--------------------------------

TRUNCATE: It removes all the records from the table permanently but table structure remains as it is.

syntax:
truncate table <tableName>;
-----------------------------------

DROP: It removes all the records from the table & as well as table structure permanently.

syntax:
drop table <tableName>;
-------------------------------------


Characterstics DML Statements:
1. DML statements are not auto saved. We use TCL statement (commit) to save the DML transactions permanently
2. we can rollback (undo) the DML transaction
3. We can apply filter OR WHERE clause for DML statements


INSERT: It is used to insert the records into the table. It can be done in 3 different approaches:

CASE 1:
  insert into <tableName> values (<val1>,<val2>,...);
  Note: 
  (a) Number of values we are entering must be similar to number of columns.
  (b) The value which we enter must be corresponding to the column datatype
  (c) The value must be entered based on the sequence of the columns present in the table.
  
  
CASE 2:
  Insert into <tableName> (<col1>,<col2>,..) values (<val1>,<val2>,...);
  
  Note: 
  (a) Here mention only the required column name for which you want to insert the values and add values to them. The remaining column automatically takes null values.
  (b) Make sure we select & add the values based on datatype & constraints.
  
  
CASE 3: Variable substitution
  Insert into <tableName> values (<&var1>,<&var2>,...);

Note: How to insert null values into the table using above approaches
-------------------------------------------------------------------------
UPDATE: It is used to modify the existing table records/Data.
It can be done in 2 ways
(a) Update only the selected records by using WHERE clause
(b) update all the records


(a) Update only the selected records using WHERE clause
syntax:
update <tableName> set <colName>=<vlaues> where <condition>;


(b) update all the records
syntax: 
update <tableName> set <colName> = <value>;
------------------------------------------
DELETE: It is used to delete the records from the table temporarily.

It can be done in 2 ways:
(a) delete only selected records using WHERE clause
(b) delete all the records


(a) delete only selected records using WHERE clause:
syntax:
delete from <TableName> where <condition>;


(b) delete all the records:
syntax:
delete from <tableName>;
----------------------------------------

TCL statements:

COMMIT: 
 - It is used to save the DML transactions permanently.
 - Commit saves ALL the unsaved transactions permanently.

syntax:
commit;
-------------------------

ROLLBACK;
 - It is used to undo (Ctrl+Z) the DML transactions
 - Only unsaved transactions can be rollback. The saved transactions cannot be rollback.

syntax:
rollback;
------------------------------

SAVEPOINT:
 - It is used to save the transactions temporarily.
 - The savepoint can be rollback. If we rollback the savepoint1, all other savepoints after the savepoint1 will be removed permanently.

syntax:
savepoint <savepointName>;

Q: How to rollback the savepoint?
Ans:
rollback to savepoint <savepointName>;
-----------------------------------------

Q: Questions on Autosave?
1. Create a table.
  - inserted 10 records
  - Updated 2 records
  - delete 5 records
  - rollback
  - commit;
Ans: 

2. Create a table
  - inserted 10 records
  - Deleted 1 record
  - rename the table name
  - deleted 4 records
  - inserted 2 records
  - rollback;
Ans:


3. Table with 10 records (saved)
 - insert 2 records
 - create a savepoint (for all the records)
 - Delete 4 records
 - alter the column datatype
 - update 3 records
 - delete all the records
 - insert 7 records
 - commit;
 - rollback to savepoint
 - delete 5 records
 - rollback
Ans:
 